const connection = require('../app/database')

class UserService {
  async create(user) {
    // 1.获取用户user
    const { account, password, nickname, roleId } = user

    // 2.拼接statement
    const statement = 'INSERT INTO `user` (account, password, nickname, identity_id) VALUES (?, ?, ?, ?);'

    // 3.执行sql语句
    const [result] = await connection.execute(statement, [account, password, nickname, roleId])

    if (result.affectedRows === 1) {
      const [user] = await this.findUserById(result.insertId)
      return user
    }
    return null
  }

  async findUserByAccount(account) {
    const statement = 'SELECT * FROM `user` WHERE account = ?;'
    const [values] = await connection.execute(statement, [account])
    return values
  }

  async findUserById(id) {
    // 根据id查询用户信息时不返回密码
    const statement = `SELECT
      user.id,
      user.nickname,
      user.account AS name,
      user.createAt AS createTime,
      JSON_OBJECT(
        'id', identity.id,
        'name', identity.name,
        'cnName', identity.cn_name
      ) AS role
      FROM user
      LEFT JOIN identity ON user.identity_id = identity.id WHERE user.id = ? AND user.is_deleted = FALSE;`
    const [values] = await connection.execute(statement, [id])
    return values
  }

  async findUserList(params) {
    const { limit, offset, roleId } = params
    const statement = `SELECT
      user.id,
      user.nickname,
      user.account AS name,
      user.createAt AS createTime,
      JSON_OBJECT(
        'id', identity.id,
        'name', identity.name,
        'cnName', identity.cn_name
      ) AS role
      FROM user
      LEFT JOIN identity ON user.identity_id = identity.id
      WHERE identity.id = ?
      AND user.is_deleted = FALSE
      ORDER BY user.id DESC
      LIMIT ? OFFSET ?;`
    const [values] = await connection.execute(statement, [String(roleId), String(limit), String(offset)])
    return values
  }

  async updateUser(user) {
    // 1.获取用户user
    const { password, nickname, id } = user
    if (!password && !nickname) return null

    let statement = 'UPDATE `user` SET password = ?, nickname = ? WHERE id = ?;'
    let values = [password, nickname, id]
    if (password && !nickname) {
      statement = 'UPDATE `user` SET password = ? WHERE id = ?;'
      values = [password, id]
    } else if (!password && nickname) {
      statement = 'UPDATE `user` SET nickname = ? WHERE id = ?;'
      values = [nickname, id]
    }
    
    const [result] = await connection.execute(statement, values)
    if (result.affectedRows === 1) {
      const [user] = await this.findUserById(id)
      return user
    }
    return null
  }

  // 删除用户（软删除）
  async deleteUser(id) {
    const statement = 'UPDATE `user` SET is_deleted = TRUE WHERE id = ?;'
    const [result] = await connection.execute(statement, [id])
    return result.affectedRows === 1
  }
}

module.exports = new UserService()